﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class relatorios_r_comercial_funil_de_vendas : System.Web.UI.Page
{
    public List<StringPair> WorkerIds
    {
        get;
        set;
    }

    public PL.Models.SellFunnelData Total
    {
        get;
        set;
    }

    protected void Page_Load(object sender, EventArgs e)
    {        
    }

    public void WriteWorkers(string toSelect, DateTime targetDate, DateTime maxDate)
    {
        this.WorkerIds = new List<StringPair>();
        PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
        PL.DataBase.DAO.Instance.Connector.Open();

        string message = "";
        try
        {
            string sql = "";
            sql += " SELECT        u.Nome, l.Cod_Usuario ";
            sql += " FROM            logs AS l INNER JOIN ";
            sql += "                          sacuser AS u ON l.Cod_Usuario = u.Código ";
            sql += " WHERE        (l.Funcao = 'Realizou atendimento') AND (l.Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') AND (l.Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') AND (u.Cod_Nivel = 2 OR ";
                                     sql += " u.Cod_Nivel = 6) AND (u.Código <> 123) ";
            sql += " GROUP BY u.Nome, l.Cod_Usuario ";
            sql += " ORDER BY u.Nome ";
            var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            while (reader.Read() == true)
            {
                StringPair workerId = new StringPair();
                workerId.Key = "" + "" + reader["Cod_Usuario"];
                workerId.Value = "" + "" + reader["Nome"];
                this.WorkerIds.Add(workerId);

                string selected = "";
                if (toSelect == ("" + reader["Cod_Usuario"]))
                {
                    selected = "selected=\"selected\"";
                }
                this.Response.Write("<option " + selected + " value=\"" + reader["Cod_Usuario"] + "\">" + reader["Nome"] + "</option>");
            }
            reader.Close();
        }
        catch (Exception e)
        {
            message = e.Message;
        }

        PL.DataBase.DAO.Instance.Connector.Close();        
    }

    public List<PL.Models.SellFunnelData> LoadFunnelData(DateTime targetDate, DateTime maxDate, string userCod )
    {
        this.Total = new PL.Models.SellFunnelData();

        List<PL.Models.SellFunnelData> allFunnelData = new List<PL.Models.SellFunnelData>();

        PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
        PL.DataBase.DAO.Instance.Connector.Open();

        if (string.IsNullOrEmpty(userCod) == true)
        {
            foreach (var worker in this.WorkerIds)
            {
                PL.Models.SellFunnelData buffer = this.GetWorkerFunnelData(targetDate, maxDate, worker.Key);                
                allFunnelData.Add(buffer);
                this.Total.Add(buffer);
            }
        }
        else
        {
            PL.Models.SellFunnelData buffer = this.GetWorkerFunnelData(targetDate, maxDate, userCod);
            allFunnelData.Add(buffer);
            this.Total.Add(buffer);
        }

        PL.DataBase.DAO.Instance.Connector.Close();
        return allFunnelData;
    }

    public PL.Models.SellFunnelData GetWorkerFunnelData(DateTime targetDate, DateTime maxDate, string userCod)
    {
        PL.Models.SellFunnelData funnelData = new PL.Models.SellFunnelData();
        try
        {            
            string sql = "";

            // Initial contact
            sql = "";
            sql += " SELECT COUNT(l.Codigo) AS TotalAtendimentos, u.Nome ";
            sql += " FROM   Logs l ";
            sql += "     INNER JOIN sacuser u ON l.Cod_Usuario = u.Código ";
            sql += " WHERE  Funcao = 'Realizou atendimento' ";
            sql += " AND (Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') ";
            sql += " AND (Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') ";
            sql += " AND  ( Cod_Nivel = 2 OR Cod_Nivel = 6 ) ";
            sql += " AND  u.Código <> 123 ";
            if (string.IsNullOrEmpty(userCod) == false)
            {
                sql += " AND (l.Cod_Usuario = " + userCod + ")";
            }
            sql += " GROUP BY u.Nome ";
            sql += " ORDER BY TotalAtendimentos ";
            var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                funnelData.Worker = "" + reader["Nome"];
                funnelData.InitialContact = "" + reader["TotalAtendimentos"];
            }
            if (string.IsNullOrEmpty(funnelData.InitialContact) == true)
            {
                funnelData.InitialContact = "0";
            }
            reader.Close();

            // Prospect
            sql = "";
            sql += " SELECT COUNT(a.Codigo) AS TOTAL, u.Nome ";
            sql += " FROM atendimento AS a  ";
            sql += "     LEFT OUTER JOIN dbo.main          AS m ON a.Cod_Cliente  = m.Código ";
            sql += "     LEFT OUTER JOIN dbo.atendsituacao AS s ON a.Situacao     = s.Codigo ";
            sql += "     INNER JOIN sacuser                AS u ON a.Cod_Vendedor = u.Código ";
            sql += "     WHERE a.Situacao >= 6 ";
            sql += "     AND a.Situacao <= 17 ";
            sql += " AND (Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') ";
            sql += " AND (Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') ";
            sql += " AND ( u.Cod_Nivel = 2 OR u.Cod_Nivel = 6 ) ";
            if (string.IsNullOrEmpty(userCod) == false)
            {
                sql += " AND (a.Cod_Vendedor = " + userCod + ")";
            }
            sql += " GROUP BY u.Nome ";
            sql += " ORDER BY Total ";

            reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                funnelData.Worker = "" + reader["Nome"];
                funnelData.Prospect = "" + reader["TOTAL"];
            }
            if (string.IsNullOrEmpty(funnelData.Prospect) == true)
            {
                funnelData.Prospect = "0";
            }
            reader.Close();

            // Qualified propects
            sql = "";
            sql += " SELECT COUNT(DISTINCT Cod_Cliente) AS TOTAL, u.Nome ";
            sql += " FROM   atendimento a  ";
            sql += "     INNER JOIN sacuser u ON a.Cod_Vendedor = u.Código ";
            sql += "     WHERE ";
            sql += " (a.Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') ";
            sql += " AND (a.Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') ";
            sql += " AND (u.Cod_Nivel = 2 OR u.Cod_Nivel = 6) ";
            sql += " AND (a.Situacao = 7 OR a.Situacao = 8 OR a.Situacao = 9) ";
            sql += " AND a.Excluido = 0 ";
            if (string.IsNullOrEmpty(userCod) == false)
            {
                sql += " AND (a.Cod_Vendedor = " + userCod + ")";
            }
            sql += " GROUP BY u.Nome ";
            sql += " ORDER BY Total ";
            reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                funnelData.Worker = "" + reader["Nome"];
                funnelData.QualifiedProspect = "" + reader["TOTAL"];
            }
            if (string.IsNullOrEmpty(funnelData.QualifiedProspect) == true)
            {
                funnelData.QualifiedProspect = "0";
            }
            reader.Close();

            // Negotiating
            sql = "";
            sql += " SELECT COUNT(DISTINCT Cod_Cliente) AS TOTAL, u.Nome ";
            sql += " FROM   atendimento a  ";
            sql += "     INNER JOIN sacuser u ON a.Cod_Vendedor = u.Código ";
            sql += "     WHERE ";
            sql += " (a.Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') ";
            sql += " AND (a.Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') ";
            sql += " AND (u.Cod_Nivel = 2 OR u.Cod_Nivel = 6) ";
            sql += " AND (a.Situacao = 7 OR a.Situacao = 8) ";
            sql += " AND a.Excluido = 0 ";
            if (string.IsNullOrEmpty(userCod) == false)
            {
                sql += " AND (a.Cod_Vendedor = " + userCod + ")";
            }
            sql += " GROUP BY u.Nome ";
            sql += " ORDER BY Total ";
            reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                funnelData.Worker = "" + reader["Nome"];
                funnelData.Negotiating = "" + reader["TOTAL"];
            }
            if (string.IsNullOrEmpty(funnelData.Negotiating) == true)
            {
                funnelData.Negotiating = "0";
            }
            reader.Close();

            // Deal
            sql = "";
            sql += " SELECT COUNT(DISTINCT Cod_Cliente) AS TOTAL, u.Nome ";
            sql += " FROM   atendimento a  ";
            sql += "     INNER JOIN sacuser u ON a.Cod_Vendedor = u.Código ";
            sql += "     WHERE ";
            sql += " (a.Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') ";
            sql += " AND (a.Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') ";
            sql += " AND (u.Cod_Nivel = 2 OR u.Cod_Nivel = 6) ";
            sql += " AND (a.Situacao = 6) ";
            sql += " AND a.Excluido = 0 ";
            if (string.IsNullOrEmpty(userCod) == false)
            {
                sql += " AND (a.Cod_Vendedor = " + userCod + ")";
            }
            sql += " GROUP BY u.Nome ";
            sql += " ORDER BY Total ";
            reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                funnelData.Worker = "" + reader["Nome"];
                funnelData.WeHaveADeal = "" + reader["TOTAL"];
            }
            if (string.IsNullOrEmpty(funnelData.WeHaveADeal) == true)
            {
                funnelData.WeHaveADeal = "0";
            }
            reader.Close();            
        }
        catch (Exception e)
        {
        }        
        return funnelData;
    }
}